<CFSET dDate = DATEFORMAT(Now(),"mm/dd/yyyy")>
<cfset theDate = DateFormat(Now(),"mm/dd/yyyy") & " " & TimeFormat(Now(),"HH:mm:ss")>
<CFMODULE TEMPLATE="#client.common_path#cfm_DBdateFormat.cfm" dateConvert="#dDate#">
<CFSET sDate1 = dateResult>
<CFSET sMessPostApproval="Leave Application has been recalled.">
<CFQUERY NAME="qryCINLEAVEAPPSRC" DATASOURCE="#client.company_dsn#">
	SELECT *
	FROM CINLEAVEAPPSRC
	WHERE ROUTEID = '#GetRecord.ROUTEID#'
</CFQUERY>
<!---
<CFQUERY NAME="qryCINLEAVEAPPSM" DATASOURCE="#client.company_dsn#">
	SELECT *
	FROM CINLEAVEAPPSM
	WHERE ROUTEID = '#GetRecord.ROUTEID#'
</CFQUERY>--->
<CFQUERY NAME="qryCINLEAVEAPPSM" DATASOURCE=#client.company_dsn#>
	SELECT STARTINGDATE,ENDINGDATE,NOOFDAYS, DOCNUMBER
	FROM CINLEAVEAPPSM
	WHERE (STARTINGDATE <= '#GetEntry.STARTINGDATE#' AND ENDINGDATE >='#GetEntry.ENDINGDATE#')
	AND PERSONNELIDNO='#GetEntry.PERSONNELIDNO#' AND (APPROVED='Y' OR APPROVED='C')
</CFQUERY>
<CFSET sDocnumber = qryCINLEAVEAPPSM.DOCNUMBER>
<CFMODULE TEMPLATE="#client.common_path#cfm_DBdateFormat.cfm" dateConvert="#qryCINLEAVEAPPSRC.STARTINGDATE#">
<CFSET sDate2 = dateResult>
<CFMODULE TEMPLATE="#client.common_path#cfm_DBdateFormat.cfm" dateConvert="#qryCINLEAVEAPPSRC.ENDINGDATE#">
<CFSET sDate3 = dateResult>
<CFIF GetEntry.STAFFPID NEQ "">
	<CFSET formPID = qryCINLEAVEAPPSRC.STAFFPID>
<CFELSE>
	<CFSET formPID = qryCINLEAVEAPPSRC.PERSONNELIDNO>
</CFIF>
<CFIF qryCINLEAVEAPPSRC.RecordCount>
	<CFQUERY NAME="qryCLKLVCODE" DATASOURCE=#client.company_dsn#>
		SELECT WITHBALANCE
		FROM CLKLVCODE
		WHERE LEAVETYPE = '#qryCINLEAVEAPPSRC.LEAVETYPE#'
	</CFQUERY>

	<CFIF NOT qryCLKLVCODE.RecordCount>
		<CFSET sMessPostApproval="WARNING!!! Leave Balance has not been updated. Leave Type doesn't exist in Leave Entitlement.<BR>Please contact your administrator.">
	<CFELSEIF Trim(Ucase(qryCLKLVCODE.WITHBALANCE)) IS "N">
		<CFSET sMessPostApproval="Leave Record has been updated.">
		<CFTRANSACTION>
		<CFTRY>
			<CFQUERY NAME="qryCINLVRECORD" DATASOURCE="#client.company_dsn#">
				INSERT INTO CINLVRECORD
					(PERSONNELIDNO, LEAVETYPE,
					 DATEENTERED, TRANCODE, LEAVESTART,
					 LEAVEEND, USERID, UNITVALUE, RECCREATEDBY, RECDATECREATED, DATELASTUPDATE, TIMELASTUPDATE)
				VALUES
					('#formPID#', '#qryCINLEAVEAPPSRC.LEAVETYPE#',
					 '#preserveSingleQuotes(theDate)#',
					 'AU',
					 #preserveSingleQuotes(sDate2)#,
					 #preserveSingleQuotes(sDate3)#,
					 '#client.userid#', '#qryCINLEAVEAPPSRC.LEAVEHOURS#',
					 '#client.userid#','#preserveSingleQuotes(dDate)#','#preserveSingleQuotes(dDate)#','#TimeFormat(Now(),"HH:mm:ss")#')
			</CFQUERY>

			<CFTRANSACTION ACTION="COMMIT"/>
			<CFCATCH TYPE="Database">
				<CFTRANSACTION ACTION="ROLLBACK"/>
				<CFSET sMessPostApproval="Error in post-approval activity ...<br><br>#CFCATCH.message# #CFCATCH.DETAIL# #cfcatch.queryError# #cfcatch.Sql#">
			</CFCATCH>
		</CFTRY>
		</CFTRANSACTION>
	<CFELSE>
		<CFQUERY NAME="qryCBR201" DATASOURCE=#client.company_dsn# MAXROWS=1>
			SELECT LEAVETRACKUNIT
			FROM CBR201
		</CFQUERY>

		<CFIF qryCBR201.RecordCount>
			<!--- Check workingdayscode --->
			<CFQUERY NAME="qryCMFPA" DATASOURCE="#client.company_dsn#">
				SELECT WORKINGDAYSCODE
				FROM CMFPA
				WHERE PERSONNELIDNO = '#formPID#'
			</CFQUERY>

			<CFIF qryCMFPA.RecordCount>
				<CFQUERY NAME="qryCLKWORKINGDAYS" DATASOURCE="#client.company_dsn#">
					SELECT WORKINGHOURSPDAY,WORKINGHOURSFORLV
					FROM CLKWORKINGDAYS
					WHERE WORKINGDAYSCODE = '#qryCMFPA.WORKINGDAYSCODE#'
				</CFQUERY>

				<CFIF qryCLKWORKINGDAYS.RecordCount>
					<CFQUERY NAME="qryCINLVENTITLEMENT" DATASOURCE="#client.company_dsn#">
						SELECT *
						FROM CINLVENTITLEMENT
						WHERE PERSONNELIDNO = '#formPID#' AND
							  LEAVETYPE = '#qryCINLEAVEAPPSRC.LEAVETYPE#'
					</CFQUERY>

					<CFIF qryCINLVENTITLEMENT.RecordCount>
						<CFIF (qryCINLEAVEAPPSRC.LEAVEHOURS NEQ "" AND qryCINLEAVEAPPSRC.LEAVEHOURS NEQ 0) OR
						(qryCINLEAVEAPPSRC.NOOFDAYS NEQ "" AND qryCINLEAVEAPPSRC.NOOFDAYS NEQ 0)>
							<CFSET nLVHours=qryCINLVENTITLEMENT.LEAVEBALANCE>
							<!--- Recall leave --->
							<!--- Start --->
							<CFTRANSACTION>
							<CFTRY>
								<!--- modified by Maan 05292007, to validate dates of leaves/no of days to recall based on leave validation
								NOOFDAYS will be the basis for the number of dates to be removed from APPSD --->
								<CFIF qryCINLEAVEAPPSM.NOOFDAYS EQ qryCINLEAVEAPPSRC.NOOFDAYS>
								<!--- recall all leaves --->
									<CFQUERY NAME="deleteAPPSM" DATASOURCE="#client.company_dsn#">
										DELETE FROM CINLEAVEAPPSM
										WHERE PERSONNELIDNO = '#formPID#' AND
										LEAVETYPE = '#qryCINLEAVEAPPSRC.LEAVETYPE#' AND DOCNUMBER = '#sDocnumber#'
									</CFQUERY>
									<CFQUERY NAME="deleteAPPSD" DATASOURCE="#client.company_dsn#">
										DELETE FROM CINLEAVEAPPSD
										WHERE DOCNUMBER = '#sDocnumber#' AND PERSONNELIDNO = '#formPID#' AND
										LEAVETYPE = '#qryCINLEAVEAPPSRC.LEAVETYPE#'
									</CFQUERY>
								<CFELSE>
									<!--- get approved leaves to recall --->
									<CFQUERY NAME="qryAPPSD" DATASOURCE="#client.company_dsn#">
										SELECT * FROM CINLEAVEAPPSD
										WHERE DOCNUMBER = '#sDocnumber#' AND PERSONNELIDNO = '#formPID#' AND
										LEAVETYPE = '#qryCINLEAVEAPPSRC.LEAVETYPE#' AND REFERENCEDATE >= '#qryCINLEAVEAPPSRC.STARTINGDATE#'
										ORDER BY REFERENCEDATE ASC
									</CFQUERY>
									<CFIF qryAPPSD.RECORDCOUNT EQ qryCINLEAVEAPPSRC.NOOFDAYS>
										<CFLOOP FROM="1" TO="#qryCINLEAVEAPPSRC.NOOFDAYS#" INDEX="count">
											<CFQUERY NAME="deleteAPPSD" DATASOURCE="#client.company_dsn#" MAXROWS="1">
												DELETE FROM CINLEAVEAPPSD
												WHERE DOCNUMBER = '#sDocnumber#' AND PERSONNELIDNO = '#formPID#' AND
												LEAVETYPE = '#qryCINLEAVEAPPSRC.LEAVETYPE#' AND REFERENCEDATE >= '#DateFormat(qryCINLEAVEAPPSRC.STARTINGDATE,"mm/dd/yy")#'
											</CFQUERY>
										</CFLOOP>
									<CFELSE>
										<CFQUERY NAME="qryAPPSD" DATASOURCE="#client.company_dsn#">
											SELECT * FROM CINLEAVEAPPSD
											WHERE DOCNUMBER = '#sDocnumber#' AND PERSONNELIDNO = '#formPID#' AND
											LEAVETYPE = '#qryCINLEAVEAPPSRC.LEAVETYPE#'
											ORDER BY REFERENCEDATE ASC
										</CFQUERY>
										<!--- start recalling  --->
										<cfset mycon = "">
										<CFLOOP QUERY="qryAPPSD">
											<CFIF qryAPPSD.REFERENCEDATE GTE GetEntry.STARTINGDATE AND qryAPPSD.REFERENCEDATE LTE GetEntry.ENDINGDATE>
												<CFQUERY NAME="deleteAPPSD" DATASOURCE="#client.company_dsn#" MAXROWS="1">
													DELETE FROM CINLEAVEAPPSD
													WHERE DOCNUMBER = '#DOCNUMBER#' AND PERSONNELIDNO = '#formPID#' AND
													LEAVETYPE = '#LEAVETYPE#' AND REFERENCEDATE = '#REFERENCEDATE#'
												</CFQUERY>
											</CFIF>
										</CFLOOP>
									</CFIF>
								</CFIF>
								<!--- get new  --->
								<CFQUERY NAME="qryAPPSD" DATASOURCE="#client.company_dsn#">
									SELECT * FROM CINLEAVEAPPSD
									WHERE DOCNUMBER = '#sDocnumber#' AND PERSONNELIDNO = '#formPID#' AND
									LEAVETYPE = '#qryCINLEAVEAPPSRC.LEAVETYPE#'
									ORDER BY REFERENCEDATE ASC
								</CFQUERY>
								<CFIF qryAPPSD.RECORDCOUNT NEQ 0> <!--- by Maan: 08242007 if there is no record left, there is nothing to update in APPSM --->
									<!--- get new starting date --->
									<CFQUERY NAME="qryAPPSD1" DATASOURCE="#client.company_dsn#" MAXROWS="1">
										SELECT * FROM CINLEAVEAPPSD
										WHERE DOCNUMBER = '#sDocnumber#' AND PERSONNELIDNO = '#formPID#' AND
										LEAVETYPE = '#qryCINLEAVEAPPSRC.LEAVETYPE#'
										ORDER BY REFERENCEDATE ASC
									</CFQUERY>
									<!--- get new ending date --->
									<CFQUERY NAME="qryAPPSD2" DATASOURCE="#client.company_dsn#" MAXROWS="#qryCINLEAVEAPPSRC.NOOFDAYS#">
										SELECT * FROM CINLEAVEAPPSD
										WHERE DOCNUMBER = '#sDocnumber#' AND PERSONNELIDNO = '#formPID#' AND
										LEAVETYPE = '#qryCINLEAVEAPPSRC.LEAVETYPE#'
										ORDER BY REFERENCEDATE DESC
									</CFQUERY>
									<!--- update CINLEAVEAPPSM --->
									<CFSET dStartDate = DateFormat(qryAPPSD1.REFERENCEDATE,"mm/dd/yy")>
									<CFSET dEndDate = DateFormat(qryAPPSD2.REFERENCEDATE,"mm/dd/yy")>
									<CFSET nNoofDays = qryAPPSD.RECORDCOUNT>
									<CFQUERY NAME="updateAPPSM" DATASOURCE="#client.company_dsn#">
										UPDATE CINLEAVEAPPSM
											SET STARTINGDATE='#preserveSingleQuotes(dStartDate)#', 
											ENDINGDATE='#preserveSingleQuotes(dEndDate)#', 
											NOOFDAYS = #nNoofDays#,
											APPROVED = 'Y'
										WHERE DOCNUMBER = '#sDocnumber#' AND PERSONNELIDNO = '#formPID#' AND
										LEAVETYPE = '#qryCINLEAVEAPPSRC.LEAVETYPE#'
									</CFQUERY>
								<CFELSE><!--- appsd no longer exists --->
									<CFQUERY NAME="deleteAPPSM" DATASOURCE="#client.company_dsn#">
										DELETE CINLEAVEAPPSM
										WHERE DOCNUMBER = '#sDocnumber#' AND PERSONNELIDNO = '#formPID#' AND
										LEAVETYPE = '#qryCINLEAVEAPPSRC.LEAVETYPE#'
									</CFQUERY>
								</CFIF>
								<CFIF qryCINLEAVEAPPSRC.WITHPAY EQ 'Y'>
								<!--- Modified by Maan, get no of days from No of Days input --->
								<CFIF qryCINLEAVEAPPSRC.WITHPAY EQ 'Y'>
									<CFIF Trim(LCase(qryCBR201.LEAVETRACKUNIT)) IS "by day">
										<CFIF qryCINLEAVEAPPSRC.LEAVEHOURS NEQ 0>
											<CFSET nValueEquivalent = abs(val(qryCINLEAVEAPPSRC.NOOFDAYS) * (val(qryCLKWORKINGDAYS.WORKINGHOURSFORLV) / val(qryCLKWORKINGDAYS.WORKINGHOURSPDAY))) + (abs(val(qryCINLEAVEAPPSRC.LEAVEHOURS)) / qryCLKWORKINGDAYS.WORKINGHOURSFORLV)>
										<CFELSE>
											<CFSET nValueEquivalent = (qryCINLEAVEAPPSRC.NOOFDAYS) * val(qryCLKWORKINGDAYS.WORKINGHOURSFORLV) / val(qryCLKWORKINGDAYS.WORKINGHOURSPDAY))>
										</CFIF>
									<CFELSE>
										<CFSET nValueEquivalent = (abs(val(qryCINLEAVEAPPSRC.NOOFDAYS)) * qryCLKWORKINGDAYS.WORKINGHOURSFORLV) + abs(val(qryCINLEAVEAPPSRC.LEAVEHOURS))>
									</CFIF>
								<CFELSE>
									<CFSET nValueEquivalent = 0>
								</CFIF>
								<!--- Orig Code
								<CFIF qryCINLEAVEAPPSRC.WITHPAY EQ 'Y'>
									<CFIF Trim(LCase(qryCBR201.LEAVETRACKUNIT)) IS "by day">
										<CFIF qryCINLEAVEAPPSRC.LEAVEHOURS NEQ 0>
											<CFSET nValueEquivalent = abs(val(nNoofDays)) + (abs(val(qryCINLEAVEAPPSRC.LEAVEHOURS)) / qryCLKWORKINGDAYS.WORKINGHOURSFORLV)>
										<CFELSE>
											<CFSET nValueEquivalent = nNoofDays>
										</CFIF>
									<CFELSE>
										<CFSET nValueEquivalent = (abs(val(nNoofDays)) * qryCLKWORKINGDAYS.WORKINGHOURSFORLV) + abs(val(qryCINLEAVEAPPSRC.LEAVEHOURS))>
									</CFIF>
								<CFELSE>
									<CFSET nValueEquivalent = 0>
								</CFIF>	--->
								<CFSET nLVBalance=nLVHours + nValueEquivalent>

								<CFIF qryCINLVENTITLEMENT.AVAILEDLEAVE NEQ "">
									<!--- <CFSET nTotAvailedLeave=qryCINLVENTITLEMENT.AVAILEDLEAVE + nValueEquivalent> --->
									<CFSET nTotAvailedLeave=qryCINLVENTITLEMENT.AVAILEDLEAVE - nValueEquivalent>
								<CFELSE>
									<CFSET nTotAvailedLeave=nValueEquivalent>
								</CFIF>
								<CFQUERY NAME="qryDeductLeave" DATASOURCE="#client.company_dsn#">
									UPDATE CINLVENTITLEMENT
									SET LEAVEBALANCE = #nLVBalance#,
										LEAVEBALANCEASOF = #preserveSingleQuotes(sDate1)#,
										AVAILEDLEAVE = '#nTotAvailedLeave#',
										AVAILEDLEAVEASOF = #preserveSingleQuotes(sDate1)#
									WHERE PERSONNELIDNO = '#formPID#' AND
										  LEAVETYPE = '#qryCINLEAVEAPPSRC.LEAVETYPE#'
								</CFQUERY>
								<CFQUERY NAME="qryCINLVRECORD" DATASOURCE="#client.company_dsn#">
									INSERT INTO CINLVRECORD
										(PERSONNELIDNO, LEAVETYPE,
										 DATEENTERED, TRANCODE, LEAVESTART,
										 LEAVEEND, UNITVALUE, VALUEEQUIVALENT,
										 ACCRUEDBALANCE, AVAILBALANCE, USERID, RECCREATEDBY, RECDATECREATED, DATELASTUPDATE, TIMELASTUPDATE)
									VALUES
										('#formPID#', '#qryCINLEAVEAPPSRC.LEAVETYPE#',
										'#preserveSingleQuotes(theDate)#', 'RC',
										 #preserveSingleQuotes(sDate2)#,
										 #preserveSingleQuotes(sDate3)#,
										 '#qryCINLVENTITLEMENT.LEAVEBALANCE#', '#nValueEquivalent#',
										 <CFIF Trim(qryCINLVENTITLEMENT.ACCRUEDBALANCE) IS "">0<CFELSE>#qryCINLVENTITLEMENT.ACCRUEDBALANCE#</CFIF>, '#nLVBalance#', '#client.userid#',
										 '#client.userid#','#preserveSingleQuotes(dDate)#','#preserveSingleQuotes(dDate)#','#TimeFormat(Now(),"HH:mm:ss")#')
								</CFQUERY>
								<CFTRANSACTION ACTION="COMMIT"/>
								<CFCATCH TYPE="Database">
									<CFTRANSACTION ACTION="ROLLBACK"/>
									<CFSET sMessPostApproval="Error in post-approval activity ...<br><br>#CFCATCH.message# #CFCATCH.DETAIL# #cfcatch.queryError# #cfcatch.Sql#">
								</CFCATCH>
							</CFTRY>
							</CFTRANSACTION>
							<!--- End --->
						<CFELSE>
							<CFSET sMessPostApproval="WARNING!!! Leave Application has not been recalled. Leave hours/days is 0.<BR>Please contact your administrator.">
						</CFIF>	<!--- LeaveType not found in CINLVENTITLEMENT --->
					<CFELSE>
						<CFSET sMessPostApproval="WARNING!!! Leave Application has not been recalled. Leave Type is missing in Leave Entitlement table.<BR>Please contact your administrator.">
					</CFIF>	<!--- LeaveType not found in CINLVENTITLEMENT --->
				<CFELSE>
					<CFSET sMessPostApproval="WARNING!!! Leave Application has not been recalled. WorkingDaysCode of employee is invalid.<BR>Please contact your administrator.">
				</CFIF> <!--- WorkingDaysCode of employee is invalid --->
			<CFELSE>
				<CFSET sMessPostApproval="WARNING!!! Leave Application has not been recalled. Employee is missing in master-file.<BR>Please contact your administrator.">
			</CFIF>	<!--- Employee not found in CMFPA --->
		<CFELSE>
			<CFSET sMessPostApproval="WARNING!!! Leave Balance has not been updated. Leave Track Unit is not properly configured.<BR>Please contact your administrator.">
		</CFIF>
	</CFIF>
<CFELSE>
	<CFSET sMessPostApproval="WARNING!!! Leave Application has not been recalled. Application for Leave Recall is missing.<BR>Please contact your administrator.">
</CFIF> <!--- RouteID not found in CINLVAPPSRC --->